﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace DBtuning
{
    public partial class Report : System.Web.UI.Page
    {

        private static DateTime dFrom;
        private static DateTime dFrom1;
        private static DateTime dTo;
        private static DateTime StartTime = new DateTime(2011,4,4);
        private static DateTime NoWayTime;
        private static System.Data.DataTable dt;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
    
		        Calendar_To.VisibleDate = DateTime.Now;
		        Calendar_From.VisibleDate = DateTime.Now.AddDays(-30);

                dFrom = DateTime.Now.AddDays(-30);
                dTo = DateTime.Now;
                NoWayTime = DateTime.Now;

                Calendar_To.SelectedDate = DateTime.Now.Date;
                Calendar_From.SelectedDate = DateTime.Now.Date.AddDays(-30);
                
            }
        }

        protected void Page_PreRender(object sender, EventArgs e)
        {
            LabelDateFrom.Text = Calendar_From.SelectedDate.ToString("yyyy-MM-dd");
            LabelDateTo.Text = Calendar_To.SelectedDate.ToString("yyyy-MM-dd");

            dFrom = Calendar_From.SelectedDate;
            dTo = Calendar_To.SelectedDate;
          
            Calendar_From.VisibleDate = dFrom;
            Calendar_To.VisibleDate = dTo;
        }

        protected void Button_View_Click(object sender, EventArgs e)
        {
            dFrom1 = dFrom;

            if (dFrom >= dTo)
            {
                Label_FB.Text = "StartingDate should be AT LEAST 1 day EARLIER than EndDate";
                GridView1.DataSource = null;
                GridView1.DataBind();
                return;
            }else
            {
                if(dTo <  StartTime)
                {
                    Label_FB.Text = "EndDate should be LATER than 2011-4-4\nBecause server run from that day";
                    GridView1.DataSource = null;
                    GridView1.DataBind();
                    return;
                }
                else if (dTo > NoWayTime)
                {
                    Label_FB.Text = "EndDate should be AT LEAST 1 day EARLIER than today";
                    GridView1.DataSource = null;
                    GridView1.DataBind();
                    return;
                }
                else if(dFrom < StartTime)
                {
                    dFrom1 = StartTime;
                }
            }

            Label_FB.Text = "Showing";

            System.Data.DataTable table = new System.Data.DataTable("Params");
            table.Columns.Add("Time", typeof(String));
            table.Columns.Add("DicCacheHit_Ratio", typeof(String));
            table.Columns.Add("SharedPool_Dic_Ratio", typeof(String));
            table.Columns.Add("BufferCacheHit_Ratio", typeof(String));
            table.Columns.Add("RedoLogBuffer_Ratio", typeof(String));
            table.Columns.Add("RedoLogSpace_Wait", typeof(String));
            table.Columns.Add("SortingArea_Ratio", typeof(String));
            table.Columns.Add("RollbackSegmentsWait_Ratio", typeof(String));

            FetchData fetch = new FetchData();
            //DataTable dt = fetch.GetReports(dFrom1, dTo);
            dt = fetch.GetReports(dFrom1, dTo);

            GridView1.DataSource = dt;
            ViewState["Data"] = dt;
            GridView1.DataBind();

           
        }

        protected void Calendar_HasAction(object sender, EventArgs e)
        {
           
            dFrom = Calendar_From.SelectedDate;
            dTo = Calendar_To.SelectedDate;
            Calendar_From.VisibleDate = dFrom;
            Calendar_To.VisibleDate = dTo;
          
        }

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            if (ViewState["Data"] == null)
                return;
            GridView1.DataSource = (System.Data.DataTable)ViewState["Data"];
            GridView1.PageIndex = e.NewPageIndex;
            GridView1.DataBind();
        }

        protected void CalendarFrom_VisibleMonthCh(object sender, MonthChangedEventArgs e)
        {
            Calendar_From.SelectedDates.Clear();
            Calendar_From.SelectedDates.Add(new DateTime(e.NewDate.Year, e.NewDate.Month, 15));
            dFrom = new DateTime(e.NewDate.Year, e.NewDate.Month, 15);
        }

        protected void CalendarTo_VisibleMonthChanged(object sender, MonthChangedEventArgs e)
        {
            Calendar_To.SelectedDates.Clear();
            Calendar_To.SelectedDates.Add(new DateTime(e.NewDate.Year, e.NewDate.Month, 15));
            dTo = new DateTime(e.NewDate.Year, e.NewDate.Month, 15);
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            DataTabletoExcel(dt,"C:\\PR\\Report.xls");
        }

         public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
    {
        if (tmpDataTable == null)
        {
            FetchData fetch = new FetchData();
            tmpDataTable = fetch.GetReports(dFrom1, dTo);
        }
        int rowNum = tmpDataTable.Rows.Count;
        int columnNum = tmpDataTable.Columns.Count;
        int rowIndex = 1;
        int columnIndex = 0;

        System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
 
        Microsoft.Office.Interop.Excel.Application xlApp = new ApplicationClass();
        xlApp.Visible = true;
        xlApp.DefaultFilePath = "";
        xlApp.DisplayAlerts = true;
        xlApp.SheetsInNewWorkbook = 1;
        Workbook xlBook = xlApp.Workbooks.Add(Missing.Value);
        
 
        //将DataTable的列名导入Excel表第一行
        foreach (DataColumn dc in tmpDataTable.Columns)
        {
            columnIndex++;
            xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
        }
 
        //将DataTable中的数据导入Excel中
        for (int i = 0; i < rowNum; i++)
        {
            rowIndex++;
            columnIndex = 0;
            for (int j = 0; j < columnNum; j++)
            {
                columnIndex++;
                xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
            }
        }
        //xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
        xlBook.SaveCopyAs(strFileName);
        //xlBook.Save();
        xlApp.Quit();
    }

    }
}
